import sqlite3
from datetime import datetime,timedelta

def get_sensor_data(db_path):
    conn = sqlite3.connect(db_path)
    c = conn.cursor()
    try:
        # 获取每个传感器的上传时间
        c.execute('SELECT sensor_address, upload_time FROM sensor_info ORDER BY id, upload_time')
        data = c.fetchall()
        return data
    except sqlite3.Error as e:
        print("数据库出现错误", e)
        return []
    finally:
        conn.close()


def calculate_timeout(sensor_address, timeout_seconds=45):
    from collections import defaultdict

    # 使用字典存储每个传感器的上传时间
    sensor_times = defaultdict(list)
    for sensor_address, upload_time in sensor_address:
        sensor_times[sensor_address].append(upload_time)

    timeout_counts = defaultdict(int)
    no_timeout_sensors = 0

    # 计算超时次数
    for sensor_address, times in sensor_times.items():
        count = 0
        for i in range(len(times) - 1):
            datetime1 = datetime.strptime(times[i], '%Y-%m-%d %H:%M:%S')
            datetime2 = datetime.strptime(times[i+1], '%Y-%m-%d %H:%M:%S')
            datetime1_str = datetime1.second
            datetime2_str = datetime2.second
            difference = datetime2_str - datetime1_str
            if difference > timeout_seconds:
                count += 1

        if count > 0:
            timeout_counts[sensor_address] = count
        else:
            no_timeout_sensors += 1

    return timeout_counts, no_timeout_sensors


def main():
    db_path = '/Users/tancongyuan/Desktop/sensor_data.db'
    sensor_data = get_sensor_data(db_path)

    if sensor_data:
        timeout_counts, no_timeout_sensors = calculate_timeout(sensor_data)
        print(f"超时次数: {timeout_counts}")
        print(f"未超时传感器数量: {no_timeout_sensors}")
    else:
        print("未能从数据库中获取数据")


if __name__ == '__main__':
    main()